package txt2mysql;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DBUtil {
    /**
     * 得到数据库连接
     *
     * @return
     * @throws Exception
     */
    private Connection getConnection() throws Exception {
        //1.创建配置文件并得到对象输入流
        InputStream is = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        //2.创建propetities
        Properties jdbc = new Properties();
        jdbc.load(is);
        //3. 通过key-value 的方式得到对应的值
        String driver = jdbc.getProperty("driver");
        String url = jdbc.getProperty("url");
        String user = jdbc.getProperty("user");
        String password = jdbc.getProperty("password");
        //4.加载运行时类对象
        Class.forName(driver);
        //5通过DriverManager得到连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;

    }

    /**
     * 释放资源的方法
     *
     * @param connection
     * @param statement
     * @param resultSet
     */
    private void release(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 查询数据库的方法
     *
     * @param sql 字符串，要执行的sql语句  如果其中有变量的话，就用  ‘"+变量+"’
     */
    public void R(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            while (resultSet.next() != false) {
                //这里可以执行一些其他的操作
                for (int i = 1; i <= columnCount; i++) {
                    System.out.println(resultSet.getString(i));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            release(connection, preparedStatement, resultSet);
        }
    }

    /**
     * 数据库记录增删改的方法
     *
     * @param sql 字符串，要执行的sql语句  如果其中有变量的话，就用  ‘"+变量+"’
     */
    public void CUD(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            release(connection, preparedStatement, null);
        }
    }

}
